package com.info.zhiduoduo.repository.mapper.partner;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.marketingcooperative.MarketingCooperativePartnerDto;
import com.info.zhiduoduo.repository.dto.partner.PartnerBriefDto;
import com.info.zhiduoduo.repository.dto.partner.PartnerExpireDto;
import com.info.zhiduoduo.repository.dto.partner.PartnerNamePhoneDto;
import com.info.zhiduoduo.repository.entity.partner.PartnerAccount;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.Date;
import java.util.List;

/**
 * <p>
 * 合伙人实名信息表 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2019-06-17
 */
public interface PartnerAccountMapper extends BaseMapper<PartnerAccount> {

    @Select("select * from zdd_partner_account where partnerid = #{partnerid}")
    PartnerAccount getPartnerAccountByPartnerId(@Param("partnerid") int partnerid);

    List<PartnerBriefDto> getVerifyingPartnerList(@Param("inviter") String inviter, @Param("realnameAuth") int realnameAuth,
                                                  @Param("name") String name, @Param("businessManager") String businessManager,
                                                  @Param("expire") int expire, @Param("whetherCrowdPartner") int whetherCrowdPartner,
                                                  @Param("crowdCostState") int crowdCostState, @Param("userName") String userName,
                                                  @Param("vaccineFlag") Integer vaccineFlag,
                                                  @Param("marketingCooperativeCompname") String marketingCooperativeCompname,
												  @Param("boundEnterprise") Integer boundEnterprise );

    @Select(" select pa.* from zdd_partner_account pa " +
            " join zdd_partner p on p.partnerid = pa.partnerid and p.deleted = 0 and p.state = 2 " +
            " where (pa.realname_auth = 4 OR pa.realname_auth = 5) ")
    List<PartnerAccount> getAllCertifiedPartner();

    @Select(" select * from zdd_partner_account " +
            " where partnerid = #{partnerid} ")
    PartnerAccount selectByPartnerId(@Param("partnerid") int partnerid);

    @Select("select p.partnerid,p.invite_code,p.createtime,p.phone,p.regfrom,p.notice_flag," +
            "pb.acctno,pa.realname,pa.promo_code,pa.idfrontimg,pa.idbackimg,pa.acctid,pa.last_season_count,pa.curr_season_count," +
            "pa2.realname as inviter,pa.work_company as workCompany,pa.city,pa.email,pa.studio_id," +
            "pa.business_manager_id,bm.business_manager as businessManager," +
            "pa.effectivedate,pa.expiredate,pa.industry,pa.occupied_time,pa.area,pa.resource,pa.scale,pa.partner_type,pa.remit_type," +
            "pa.partner_remark,pa.extra_info,pa.crowd_cost_state,pa.crowd_cost_primary_rate,pa.crowd_cost_secondary_rate," +
		    "pa.crowd_cost_secondary_amount,pa.vaccine_compid,c.compname as vaccineCompname,pa.vaccine_poster,pa.vaccine_flag  " +
            "from zdd_partner p " +
            "LEFT JOIN zdd_partner_account pa on pa.partnerid = p.partnerid " +
            "LEFT JOIN zdd_partner_bankcard pb on pb.partnerid = p.partnerid and pb.state = 1 " +
            "LEFT JOIN zdd_partner_account pa2 on pa2.promo_code = p.invite_code and p.invite_code != '' " +
            "LEFT JOIN zdd_business_manager bm on bm.business_manager_id = pa.business_manager_id " +
		    "LEFT JOIN zdd_company c on c.compid = pa.vaccine_compid " +
            "where p.deleted = 0  and p.state = 4 " +
            "AND IFNULL(pa.realname,'') like '%${name}%' " +
            "AND IF(ISNULL(#{inviter}) or #{inviter}='',1=1,CONCAT(pa2.promo_code,pa2.realname) like '%${inviter}%') " +
            "AND IF(ISNULL(#{businessManager}) or #{businessManager}='', 1=1, bm.business_manager like '%${businessManager}%' )" +
            "AND p.deleted = 0 " +
            "order by p.partnerid desc ")
    List<PartnerBriefDto> getRescissionList(@Param("inviter") String inviter, @Param("name") String name, @Param("businessManager") String businessManager);

    @Select("select max(totalBrokerage) totalBrokerage,`month`," +
            "p.partnerid,p.invite_code,p.createtime,p.phone,p.regfrom,p.notice_flag," +
            "pb.acctno,pa.realname,pa.promo_code,pa.idfrontimg,pa.idbackimg,pa.acctid,pa.last_season_count,pa.curr_season_count," +
            "pa2.realname as inviter,pa.work_company as workCompany,pa.city,pa.email,pa.studio_id," +
            "pa.business_manager_id,bm.business_manager as businessManager," +
            "pa.effectivedate,pa.expiredate,pa.industry,pa.occupied_time,pa.area,pa.resource,pa.scale,pa.partner_type,pa.remit_type," +
            "pa.partner_remark,pa.extra_info,pa.crowd_cost_state,pa.crowd_cost_primary_rate,pa.crowd_cost_secondary_rate," +
		    "pa.crowd_cost_secondary_amount,pa.vaccine_compid,c.compname as vaccineCompname,pa.vaccine_poster,pa.vaccine_flag  " +
            "from (select pb.partnerid,sum(pb.brokerage) totalBrokerage,DATE_FORMAT(createtime,'%Y%m') `month` " +
            "   from zdd_partner_brokerage pb " +
            "   where pb.deleted = 0 and DATE(createtime) >= #{date}  " +
            "   GROUP BY pb.partnerid,DATE_FORMAT(createtime,'%Y%m')" +
            "   having sum(pb.brokerage) >= #{amount}) tpb " +
            "join zdd_partner p on p.partnerid = tpb.partnerid " +
            "LEFT JOIN zdd_partner_account pa on pa.partnerid = p.partnerid " +
            "LEFT JOIN zdd_partner_bankcard pb on pb.partnerid = p.partnerid and pb.state = 1 " +
            "LEFT JOIN zdd_partner_account pa2 on pa2.promo_code = p.invite_code and p.invite_code != '' " +
            "LEFT JOIN zdd_business_manager bm on bm.business_manager_id = pa.business_manager_id " +
		    "LEFT JOIN zdd_company c on c.compid = pa.vaccine_compid " +
            "where p.deleted = 0 and pa.realname_auth = 4 and p.state != 4 " +
            "AND IFNULL(pa.realname,'') like '%${name}%' " +
            "AND IF(ISNULL(#{inviter}) or #{inviter}='',1=1,CONCAT(pa2.promo_code,pa2.realname) like '%${inviter}%') " +
            "AND IF(ISNULL(#{businessManager}) or #{businessManager}='', 1=1, bm.business_manager like '%${businessManager}%' ) " +
            "AND p.deleted = 0 " +
            "GROUP BY tpb.partnerid " +
            "order by pa.remit_type,p.partnerid desc ")
    List<PartnerBriefDto> listBrokerageExceedPartners(@Param("inviter") String inviter, @Param("name") String name,
                                                      @Param("businessManager") String businessManager, @Param("date") Date date,
                                                      @Param("amount") int amount);

    @Select("select p.partnerid,p.invite_code,p.createtime,p.phone,p.regfrom,p.notice_flag," +
            "pb.acctno,pa.realname,pa.promo_code,pa.idfrontimg,pa.idbackimg,pa.acctid,pa.last_season_count,pa.curr_season_count," +
            "pa2.realname as inviter,pa.work_company as workCompany,pa.city,pa.email," +
            "pa.business_manager_id,bm.business_manager as businessManager," +
            "pa.effectivedate,pa.expiredate,pa.industry,pa.occupied_time,pa.area,pa.resource,pa.scale,pa.partner_type,pa.remit_type," +
            "pa.partner_remark,pa.extra_info " +
            "from zdd_partner p " +
            "LEFT JOIN zdd_partner_account pa on pa.partnerid = p.partnerid " +
            "LEFT JOIN zdd_partner_bankcard pb on pb.partnerid = p.partnerid and pb.state = 1 " +
            "LEFT JOIN zdd_partner_account pa2 on pa2.promo_code = p.invite_code and p.invite_code != '' " +
            "LEFT JOIN zdd_business_manager bm on bm.business_manager_id = pa.business_manager_id " +
            "where p.deleted = 0  and p.state = 2 " +
            "and pa.realname_auth = 4 " +
            "and pa.expiredate <= #{expiredate}" +
		    "AND IFNULL(pa.realname,'') like '%${name}%' " +
		    "AND IF(ISNULL(#{inviter}) or #{inviter}='',1=1,CONCAT(pa2.promo_code,pa2.realname) like '%${inviter}%') " +
		    "AND IF(ISNULL(#{businessManager}) or #{businessManager}='', 1=1, bm.business_manager like '%${businessManager}%' )" +
            "order by pa.effectivedate ")
    List<PartnerExpireDto> getPendingExpirePartnerList(@Param("inviter") String inviter, @Param("name") String name,
                                                       @Param("businessManager") String businessManager,
                                                       @Param("expiredate") Date expiredate);

    @Update(" update zdd_partner_account set vaccine_flag = #{state} where partnerid in(${partnerids})")
	int vaccineStatus(String partnerids, int state);

	@Select(" select distinct(p.partnerid) as partnerid, pa.realname, p.phone,pa.audit_pass_time as registerTime " +
			" from zdd_partner_account pa " +
			" join zdd_partner p on p.partnerid = pa.partnerid " +
			" where pa.vaccine_compid = #{compid} " +
			" and vaccine_flag = 1 " +
			" and pa.realname like '%${name}%' " +
			" and p.phone like '%${phone}%' ")
	List<PartnerNamePhoneDto> listVaccinePartners(int compid, String name, String phone);

	@Select("SELECT COUNT(pa.partnerid) " +
			"FROM zdd_partner_account pa " +
			"LEFT JOIN zdd_marketing_cooperative mc ON mc.partnerid = pa.partnerid " +
			"WHERE pa.realname_auth = 2 " +
			"AND mc.compid = #{compid} ")
	int getPartners(@Param("compid") int compid);

}
